/**
 * 构建sql语句，仅仅是构建，不会执行哦
 *
 * 用这里的方法构建的sql语句，会是这种形式返回：
 * {
 * sql: 'SELECT * FROM `books` WHERE `author` = ?',
 * values: ['David']
 * }
 *
 * 这种格式可以直接传入mysql的query的第一个参数
 * */
import {flatten, trim} from './lib/tools'
import {quotK, leftJoin, returnData, whereMain, updateSet} from './sql-tools'
import {ObjectT, WhereT} from './types'

/**
 * 对应sql的insert
 * */
export function insert(table: string, data: ObjectT | ObjectT[]) {
  // const isMult = data instanceof Array
  if (!(data instanceof Array)) {
    data = [data]
  }
  // insert into `coords` (`x`, `y`) values (20, DEFAULT), (DEFAULT, 30), (10, 20)

  const values: (number | string)[] = []

  const fieldsTemp: string[][] = data.map(item => {
    return Object.keys(item)
  })

  // 拉平+去重
  const fields: string[] = Array.from(new Set(flatten(fieldsTemp)))

  const dataArr: string[][] = []
  data.forEach(item => {
    dataArr.push(
      fields.map(field => {
        if (item[field] === undefined || item[field] === null) {
          return 'DEFAULT'
        }
        // @ts-ignore
        values.push(item[field])
        return '?'
      })
    )
  })

  const sql = `INSERT INTO \`${table}\` (\`${fields.join(
    '`,`'
  )}\`) VALUES (${dataArr.join('),(')})`
  return returnData(sql, values)
}

/**
 * 对应sql的 DELETE FROM
 * */
export function del(table: string, where: any, limit = 1, allowEmpty = false) {
  const [whereSqls, vals2] = whereMain(where)
  if (!(whereSqls || allowEmpty)) {
    return returnData('', [])
  }
  const sql = `DELETE FROM \`${table}\`${
    whereSqls ? ' WHERE ' + whereSqls : ''
  }${limit ? ` LIMIT ${limit}` : ''}`
  return returnData(sql, vals2)
}

/**
 * 生成update语句
 * */
export function update(
  table: string,
  where: any,
  data: any,
  limit = 1,
  allowEmpty = false
) {
  const [colsSqls, vals1] = updateSet(data)
  if (!colsSqls.length) {
    return returnData('', [])
  }
  const [whereSqls, vals2] = whereMain(where)
  if (!(whereSqls.length || allowEmpty)) {
    return returnData('', [])
  }
  const sql = `UPDATE \`${table}\` SET ${colsSqls.join(',')}${
    whereSqls ? ' where ' + whereSqls : ''
  }${limit ? ` LIMIT ${limit}` : ''}`
  return returnData(sql, [...vals1, ...vals2])
}

// 生成select语句
export function select(
  table: string,
  where: WhereT | string | [string, (number | string)[]],
  col?: string | string[],
  order?: null | {[k: string]: 'DESC' | 'ASC' | string},
  limit: number = 0,
  offset: number = 0,
  groupBy?: string
) {
  // 为了做到更多功能，where 参数已经不仅仅是筛选条件了，还可以承载left join等特殊参数
  let asTable: any
  let leftJoins: any
  let myWhere = where
  if (typeof where === 'object' && !Array.isArray(where)) {
    ;({AS: asTable, 'LEFT JOIN': leftJoins = [], ...myWhere} = where)
  }

  const leftJoinStr = leftJoin(leftJoins)

  const defTable = leftJoinStr ? asTable || table : ''

  // 选择的列
  let colsArr: string[] = []
  if (col) {
    if (typeof col === 'string') {
      colsArr.push(col)
    } else {
      colsArr = col
    }
    colsArr = colsArr.map(c => quotK(c, defTable))
  }

  // 排序
  const orderArr = []
  if (order) {
    for (const [k, v] of Object.entries(order)) {
      const ks = k.split('#')

      if (ks[1] === 'function') {
        // 最新用法：{'#function': 'RAND()'}
        // ps. function前必须加一个#号，因为发现function是允许作为列名的
        orderArr.push(v)
      } else {
        const key = quotK(trim(ks[0]), defTable)
        orderArr.push(`${key} ${v.toUpperCase()}`)
      }
    }
  }

  const [whereStr, vals] = whereMain(myWhere, defTable)

  const sql =
    `SELECT ${colsArr.join(',') || '*'} FROM \`${table}\`` +
    (asTable ? ` AS ${asTable}` : '') +
    (leftJoinStr ? ` ${leftJoinStr}` : '') +
    (whereStr ? ' WHERE ' + whereStr : '') +
    (groupBy ? ' GROUP BY ' + groupBy : '') + // GROUP BY 必须在 ORDER BY 前，否则会报sql错误
    (orderArr.length ? ' ORDER BY ' + orderArr.join(',') : '') +
    (limit ? ' LIMIT ' + limit : '') +
    (offset ? ' OFFSET ' + offset : '')
  return returnData(sql, vals)
}
